﻿using System;
using System.Data;
using DotNetNuke.Framework.Providers;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;

namespace DNNToolkit.Modules.CityTaxLocator.Components
{
    /// <summary>
    /// The SqlDataProvider class is a SQL Server implementation of the abstract DataProvider.
    /// </summary>
    public class SqlDataProvider : DataProvider
    {
        #region "Membres Prives"

        private const string ProviderType = "data";
        private const string ModuleQualifier = "DNNToolkit_";

        private ProviderConfiguration _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
        private string mConnectionString;
        private string mProviderPath;
        private string mObjectQualifier;
        private string mDatabaseOwner;

        #endregion

        #region Constructors

        /// <summary>
        /// Constructs new SqlDataProvider instance.
        /// </summary>
        public SqlDataProvider()
        {
            //Read the configuration specific information for this provider
            Provider objProvider = (Provider)_providerConfiguration.Providers[_providerConfiguration.DefaultProvider];

            //Read the attributes for this provider
            if (!string.IsNullOrEmpty(objProvider.Attributes["connectionStringName"]) && !string.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings[objProvider.Attributes["connectionStringName"]]))
            {
                mConnectionString = System.Configuration.ConfigurationManager.AppSettings[objProvider.Attributes["connectionStringName"]];
            }
            else
            {
                mConnectionString = objProvider.Attributes["connectionString"];
            }

            mProviderPath = objProvider.Attributes["providerPath"];

            mObjectQualifier = objProvider.Attributes["objectQualifier"];

            if (!string.IsNullOrEmpty(mObjectQualifier) && (mObjectQualifier.EndsWith("_") == false))
            {
                mObjectQualifier += "_";
            }

            mDatabaseOwner = objProvider.Attributes["databaseOwner"];
            if (!string.IsNullOrEmpty(mDatabaseOwner) && (mDatabaseOwner.EndsWith(".") == false))
            {
                mDatabaseOwner += ".";
            }
            DataConnection = new SqlConnection(mConnectionString);
        }
        #endregion

        #region Properties

        /// <summary>
        /// Gets and sets the Provider path.
        /// </summary>
        public string ProviderPath
        {
            get { return mProviderPath; }
        }

        /// <summary>
        /// Gets and sets the Object qualifier.
        /// </summary>
        public string ObjectQualifier
        {
            get { return mObjectQualifier; }
        }

        /// <summary>
        /// Gets and sets the database ownere.
        /// </summary>
        public string DatabaseOwner
        {
            get { return mDatabaseOwner; }
        }


        #endregion

        #region "Methodes Privees"

        private string GetFullyQualifiedName(string name)
        {
            return DatabaseOwner + ObjectQualifier + ModuleQualifier + name;
        }


        #endregion

        #region "Methods for table DNNToolkit_CityTax"

        public override void CreateCityTax(string state, string localName, string locRate, string schlRate,
            string whRate, string w2Name, string localCD, string resInd,
            string countyName, string additionalJur, string comments)
        {
            SqlHelper.ExecuteNonQuery((SqlTransaction)DataTransaction, GetFullyQualifiedName("CreateCityTax"),
                state, localName, locRate, schlRate, whRate, w2Name,
                localCD, resInd, countyName, additionalJur, comments);
        }

        public override void UpdateCityTaxById(long citytaxid, string state, string localName, string locRate, string schlRate,
            string whRate, string w2Name, string localCD, string resInd,
            string countyName, string additionalJur, string comments)
        {
            SqlHelper.ExecuteNonQuery((SqlTransaction)DataTransaction, GetFullyQualifiedName("UpdateCityTaxById"),
                citytaxid, state, localName, locRate, schlRate, whRate,
                w2Name, localCD, resInd, countyName, additionalJur, comments);
        }

        public override void DeleteCityTax()
        {
            SqlHelper.ExecuteNonQuery((SqlTransaction)DataTransaction, GetFullyQualifiedName("DeleteCityTax"), null);
        }

        public override void DeleteCityTaxByID(long cityTaxID)
        {
            SqlHelper.ExecuteNonQuery((SqlTransaction)DataTransaction, GetFullyQualifiedName("DeleteCityTaxByID"), cityTaxID);
        }

        public override IDataReader SearchCityTaxByCity(string state, string city)
        {
            return (IDataReader)SqlHelper.ExecuteReader((SqlConnection)DataConnection, GetFullyQualifiedName("SearchCityTaxByCity"), state, city);
        }

        public override IDataReader SearchCityTaxByCode(string localCD)
        {
            return (IDataReader)SqlHelper.ExecuteReader((SqlConnection)DataConnection, GetFullyQualifiedName("SearchCityTaxByCode"), localCD);
        }

        public override IDataReader SearchCityTaxStates()
        {
            return (IDataReader)SqlHelper.ExecuteReader((SqlConnection)DataConnection, GetFullyQualifiedName("SearchCityTaxStates"), null);
        }

        public override IDataReader GetAllCityTax()
        {
            return (IDataReader)SqlHelper.ExecuteReader((SqlConnection)DataConnection, GetFullyQualifiedName("GetAllCityTax"), null);
        }

        public override IDataReader GetCityTaxByID(long cityTaxID)
        {
            return (IDataReader)SqlHelper.ExecuteReader((SqlConnection)DataConnection, GetFullyQualifiedName("GetCityTaxByID"), cityTaxID);
        }

        public override int SearchCityTaxCount()
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar((SqlConnection)DataConnection, GetFullyQualifiedName("SearchCityTaxCount"), null));
        }

        #endregion

        #region "Methods for table DNNToolkit_CityTaxComment"

        public override void CreateCityTaxComment(DateTime lastUpdate, string additionComment)
        {
            SqlHelper.ExecuteNonQuery((SqlTransaction)DataTransaction, GetFullyQualifiedName("CreateCityTaxComment"), lastUpdate, additionComment);
        }

        public override void DeleteCityTaxComment()
        {
            SqlHelper.ExecuteNonQuery((SqlTransaction)DataTransaction, GetFullyQualifiedName("DeleteCityTaxComment"), null);
        }

        public override IDataReader SearchCityTaxComment()
        {
            return (IDataReader)SqlHelper.ExecuteReader((SqlConnection)DataConnection, GetFullyQualifiedName("SearchCityTaxComment"), null);
        }

        #endregion
    }
}